<?php

/**
 * Description of Actor
 *
 * @author Scott McKay
 */
class Application_Model_Movie extends Zend_Db_Table_Abstract {

    protected $_name = 'Movie';

    public function getMovie($id) {
        $sql = "SELECT Title FROM Movie WHERE Movie_ID = ?";
        $params = array($id);
        return $this->getAdapter()->query($sql, $params)->fetchAll();
    }
    
    public function getMovie2($id) {
        $sql = "SELECT * FROM Movie WHERE Movie_ID = ?";
        $params = array($id);
        $values = $this->getAdapter()->query($sql, $params)->fetchAll();
        return $values[0];
    }
    
    public function getMovies() {
        $sql = "SELECT Title, Movie_ID FROM Movie";
        return $this->getAdapter()->query($sql)->fetchAll();
    }
    
    public function getMovies2() {
        $sql = "SELECT * FROM Movie";
        return $this->getAdapter()->query($sql)->fetchAll();
    }
    
    public function getTop5() {
		$sql = "SELECT AVG(Grade) AS 'Rank', Title FROM Movie m, Comment c, Watches w WHERE" .
				" c.Comment_ID = w.Comment_ID AND " .
				" m.Movie_ID = w.Movie_ID " .
				" GROUP BY w.Movie_ID ORDER BY Rank DESC";
		
       // $sql = "SELECT Title FROM Movie  ORDER BY Ranking DESC LIMIT 0,5";
        return $this->getAdapter()->query($sql)->fetchAll();
    }
    
    public function getAVGRank($id) {
		
		$sql = "SELECT AVG(Grade) AS 'RANK' FROM Comment JOIN Watches ON Comment.Comment_ID = Watches.Watches_ID WHERE Movie_ID = ?";
		$params = array($id);
		return $this->getAdapter()->query($sql)->fetchAll();
	}
    
    public function search($string) {
        $sql = "SELECT Title, Movie_ID FROM Movie WHERE Title LIKE ? LIMIT 0,7";
        $params = array("%".$string."%");
        return $this->getAdapter()->query($sql, $params)->fetchAll();
    }
    
    /**
     * This function will store a new movie in the database
     * 
     * @param Title The movie title
     * @param ESRB The ESRB rating of tthe movie
     * @param D_Release The release date of the movie
     * @param Poster The movie's poster picture
     * @param Ranking The movie ranking
     * @param RunningTime The movies total run time
     * @param Description A description of the movie
     * @param
     */
    public function addMovie($Title, $ESRB, $D_Release, $Poster, $Ranking, $RunningTime, $Description){
		$sql = "INSERT INTO Movie (Movie_ID, Title, ESRB, D_Release, Poster, Ranking, RunningTime, Description) VALUES
				(Null, :title, :esrb, :drelease, :poster, :ranking, :runtime, :desc)";
		$params = array(":title" => $Title, ":esrb" => $ESRB, ":drelease" => $D_Release, ":poster" => $Poster, ":ranking" => $Ranking, ":runtime" => $RunningTime, ":desc" => $Description);
		$this->getAdapter()->query($sql,$params);
		//return $this->getAdapter()->query($sql,$params)->fetchAll();
	}
	
	public function updateMovie($Movie_ID, $Title, $ESRB, $D_Release, $Poster, $Ranking, $RunningTime, $Description) {
		$sql = "UPDATE Movie m SET m.Title = :title, m.ESRB = :esrb, m.D_Release = :drelease, m.Poster = :poster, m.Poster = :poster, m.Ranking = :ranking, m.RunningTime = :runtime, m.Description = :desc WHERE m.Movie_ID = :id";
		$params = array(":title" => $Title, ":esrb" => $ESRB, ":drelease" => $D_Release, ":poster" => $Poster, ":ranking" => $Ranking, ":runtime" => $RunningTime, ":desc" => $Description, ":id" => $Movie_ID);
		$this->getAdapter()->query($sql,$params);
	}
	
	public function deleteMovie($id){
		$sql = "DELETE FROM Movie WHERE Movie_ID = ?";
		$params = array($id);
		$this->getAdapter()->query($sql,$params);
	}
	
}

?>
